FIG. 1 is a block diagram of a database system 10. The system includes an application 12 which communicates with a database server 14. The application 12 includes host language and SQL statements which are utilized to communicate with the server to obtain the appropriate information from the tables therein. The database server 14 typically prepares or converts the host language to allow the application to be executed (by the server). This preparation or process is referred to as preparing a package. A package is typically identified uniquely by an application program identifier (application ID) and a collection identifier (collection ID).
The server includes a memory 20 and an optimizer 22. The optimizer 22 provides the best access path for the information from tables of a user database 16 for a particular package from a plurality of packages 26 in a DB2 catalog 18. The best access path is provided via a bind process. The server 14 is in turn in communication with the DB2 catalog 18 and a user database 16.
The ability to switch between various packages and provide package resolution on a database server is a useful function for SQL applications. For example, it may be desirable to create multiple collection IDs containing the same package name in order to allow different bind options or SQL access paths to be in effect. Certain database management systems such as DB2 Universal Database for z/OS by IBM Corporation have offered solutions that involve a list of possible package collections that the SQL applications are bound into.
The database server would use this list to locate a matching package, where the list of packages is specified during static bind of the application plan and thus not changeable during execution time. Further, execution environments where remote clients are connected do not have the ability to run with application plans, thus the ability to switch between packages is very limited.
One solution is for the application to specify to the database server its package collection via a SQL statement, referred to as SET CURRENT PACKAGESET. The drawback with this solution is that multiple programs within the application need to coordinate the setting of the CURRENT PACKAGESET value, and it is difficult to keep the correct value in the CURRENT PACKAGESET register of the application at all times when the application is complex (e.g. lots of separately compiled programs that make up a large application).
Given a set of packages such as JDBC_COLLECTION, SQLJ_COLL1 and SQLJ_COLL2 when utilizing the SET CURRENT PACKAGESET statement, a programmer would have to code application logic in the following manner when utilizing the SET CURRENT PACKAGESET statement:    SET CURRENT PACKAGESET=“JDBC_COLLECTION”    perform some JDBC logic    SET CURRENT PACKAGESET=“SQLJ_COLL1”    run SQLJ logic    SET CURRENT PACKAGESET=“JDBC_COLLECTION”    perform more JDBC logic    SET CURRENT PACKAGESET=“SQLJ_COLL2”
Run SQLJ logic
Accordingly, the application developer is only able to specify a single package collection name at any given point in time via the CURRENT PACKAGESET. As the application moved from subroutine to subroutine, the application developer had to be very careful to set and restore the CURRENT PACKAGESET value properly so that the current package collection was in effect at all times.
With the conventional database system, a plan's package list was managed by the client. If the client's package collection list contained “A, B, C, D”, the client would flow the SQL statement across the network once for each package collection until the correct package containing the SQL statement is found. Thus in the worst case, the client would send the SQL statement to the server four times before finding a matching package.
Another problem with the list of package collections associated with each plan is observed in the stored procedure and user-defined function execution environment. Stored procedures and user-defined functions are often stand-alone sets of programs and invoked by multiple callers. However, users cannot identify package resolution schemes independently from the rules established by the plan, because the plan's list is always used to search packages as the stored procedures are executed. A solution for package management is needed that will allow nested storage procedures or user-defined functions to be implemented without concern for the caller's runtime environment.
Accordingly, what is needed is a system and method for package resolution in a database system that overcomes the above-identified problems. The present invention addresses such a need.